package com.dotflat.satistics.repository.crm.impl;

import com.dotflat.satistics.repository.crm.ClienteleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * ********************
 * Created by Ron
 * from com.matt
 * on 2018/8/28 0028.
 * ********************
 */
@Repository
public class ClienteleRepositoryImpl implements ClienteleRepository {

    @Autowired
    private JdbcTemplate crmJdbcTemplate;


    @Override
    public Long findByTypeSignAndChannelAndStatus(Date begin, Date end, String areaId, String channelId,int status) {
        Long count = crmJdbcTemplate.queryForObject("SELECT count(DISTINCT(cl.id)) from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId WHERE con.created >= ? AND con.created <= ? and cl.areaId = ? AND cl.channelId = ? and con.status != ? ",new Object[]{begin, end, areaId, channelId,status}, Long.class);
        return count;
    }

    @Override
    public Long findByTypeAndSignAndStatus(Date begin, Date end, String areaId,int status) {
        Long count = crmJdbcTemplate.queryForObject("SELECT count(DISTINCT(cl.id)) from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId WHERE con.created >= ? AND con.created <= ? and cl.areaId = ? and con.status != ? ",new Object[]{begin, end, areaId,status}, Long.class);
        return count;
    }

    @Override
    public Long findByCancelClientele(Date begin, Date end, String areaId, int status) {
        Long count = crmJdbcTemplate.queryForObject("SELECT count(DISTINCT(cl.id)) from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId WHERE con.created >= ? AND con.created <= ? and cl.areaId = ? AND con.status = ? ",new Object[]{begin, end, areaId,status}, Long.class);
        return count;
    }

    @Override
    public Double findBreakDownByTypeAndStatus(Date begin, Date end, String areaId, int status) {
        Double count = crmJdbcTemplate.queryForObject("SELECT count(DISTINCT(cl.id)) from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId WHERE con.created >= ? AND con.created <= ? and cl.areaId = ? and con.status = ? ",new Object[]{begin, end, areaId,status}, Double.class);
        return count;
    }

    @Override
    public Long findByTypeSignAndChannelAndStatusAndIsrewal(Date begin, Date end, String areaId, String channelId, int status, int contractNonRenewal) {
        Long count = crmJdbcTemplate.queryForObject("SELECT count(DISTINCT(cl.id)) from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId WHERE con.created >= ? AND con.created <= ? and cl.areaId = ? AND cl.channelId = ? and con.status != ? and con.isRenewal =?  ",new Object[]{begin, end, areaId, channelId,status,contractNonRenewal}, Long.class);
        return count;
    }

    @Override
    public Long findByTypeAndSignAndStatusAndIsrewal(Date begin, Date end, String areaId, int status, int contractNonRenewal) {
        Long count = crmJdbcTemplate.queryForObject("SELECT count(DISTINCT(cl.id)) from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId WHERE con.created >= ? AND con.created <= ? and cl.areaId = ? and con.status != ? and con.isRenewal =? ",new Object[]{begin, end, areaId,status,contractNonRenewal}, Long.class);
        return count;
    }

    @Override
    public Double findByCloseTime(Date begin, Date end,String areaId) {
        String sql = "SELECT sum(TIMESTAMPDIFF(HOUR,le.created,con.paidDate)) FROM leads as le INNER JOIN clientele as cl on le.id=cl.leadsId INNER JOIN contract as con on cl.id = con.clienteleId " +
                " WHERE con.created >=? and con.created <=? and con.payStatus=1 and con.`status` !=2 and con.areaId=?";
        Double count  = crmJdbcTemplate.queryForObject(sql,new Object[]{begin, end, areaId}, Double.class);
        return Objects.isNull(count) ? 0d : count;
    }

    @Override
    public Long findWillingRenewalCountByStatusAndContractTypeAndAreaId(int status, int type, String areaId) {
        String sql = "select count(DISTINCT(cp.clienteleId)) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId  inner join contract as con on con.id=cp.contractId " +
                " where con.type=? and TO_DAYS(con.endDate)-TO_DAYS(NOW())<30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0 and con.status=? and cli.areaId=?";
        Long count = crmJdbcTemplate.queryForObject(sql,new Object[]{type,status,areaId},Long.class);
        return count;
    }

    @Override
    public Long findRenewalCountByStatusAndContractTypeAndAndRenewalAndAreaId(int status, int nurseryType,int isRenewal, String areaId) {
        String sql = "select count(DISTINCT(cl.id)) from clientele as cl left join contract as con on cl.id=con.clienteleId where con.status IN(0,1) and con.isRenewal=? and TO_DAYS(con.endDate)-TO_DAYS(NOW()) >=30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0" +
                " and con.clienteleId IN(select DISTINCT(cp.clienteleId) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId  inner join contract as con on con.id=cp.contractId " +
                " where con.type=? and TO_DAYS(con.endDate)-TO_DAYS(NOW())<30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0 and con.status=? and cli.areaId=?)";
        Long count = crmJdbcTemplate.queryForObject(sql,new Object[]{isRenewal,nurseryType,status,areaId},Long.class);
        return count;
    }

    @Override
    public Long findRenewalCountByStatusAndContractTypeAndAndRenewalAndStaffId(int status, int nurseryType, int isRenewal, String staffId,String clienteleIds) {
        String sql = "select count(DISTINCT(cl.id)) from clientele as cl left join contract as con on cl.id=con.clienteleId where con.status IN(0,1) and con.isRenewal=? and TO_DAYS(con.endDate)-TO_DAYS(NOW()) >=30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0" +
                " and con.clienteleId IN(select DISTINCT(cp.clienteleId) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId  inner join contract as con on con.id=cp.contractId " +
                " where con.type=? and TO_DAYS(con.endDate)-TO_DAYS(NOW())<30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0 and con.status=? and cli.staffId=? and cli.id IN(?))";
        Long count = crmJdbcTemplate.queryForObject(sql,new Object[]{isRenewal,nurseryType,status,staffId,clienteleIds},Long.class);
        return count;
    }

    @Override
    public Long findWillingRenewalCountByStatusAndContractTypeAndStaffId(int status, int type, String staffId,String clienteleIds) {
        String sql = "select count(DISTINCT(cp.clienteleId)) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId  inner join contract as con on con.id=cp.contractId " +
                " where con.type=? and TO_DAYS(con.endDate)-TO_DAYS(NOW())<30 and TO_DAYS(con.endDate)-TO_DAYS(NOW())>0 and con.status=? and cli.staffId=? and cli.id IN(?)";
        Long count = crmJdbcTemplate.queryForObject(sql,new Object[]{type,status,staffId,clienteleIds},Long.class);
        return count;
    }

    @Override
    public Long findWillingRenawalEarlyCountByStatusAndTypeAndRenewalAndAreaId(int status, int type, String areaId) {
        String sql="select count(DISTINCT(cp.clienteleId)) AS cId from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId INNER JOIN contract as con ON cp.contractId=con.id " +
                " where con.type=? and con.status=? and cp.remainCount<8 and cp.remainCount>0 and cli.areaId=?";
        Long count = crmJdbcTemplate.queryForObject(sql,new Object[]{type,status,areaId},Long.class);
        return count;
    }

    @Override
    public Long findEarlyRenewalCountByStatusAndContractTypeAndRenewalAndAreaId(int status, int type, int isRenewal, String areaId) {
        String sql = "select count(DISTINCT(cl.id)) from clientele as cl left join contract as con on cl.id=con.clienteleId INNER JOIN contract_product as cp on con.id=cp.contractId where con.status IN(0,1) and con.isRenewal=? and  cp.remainCount>=8 and cp.remainCount>0" +
                " and con.clienteleId IN(select DISTINCT(cp.clienteleId) from contract_product as cp inner join product as pr on cp.productId=pr.id inner join clientele as cli on cli.id=cp.clienteleId  " +
                " inner join contract as con on con.id=cp.contractId where con.type=? and cp.remainCount<8 and cp.remainCount>0 and con.status=? and cli.areaId=?)" ;
        Long count = crmJdbcTemplate.queryForObject(sql,new Object[]{isRenewal,type,status,areaId},Long.class);
        return count;
    }

    /**
     *
     * @param staffId
     * @param date format yyyy-MM
     * @return
     */
    @Override
    public List<Map<String, Object>> findByStaffId(String staffId, String date) {

        String sql = "SELECT * FROM clientele " +
                " WHERE staffId = ? " +
                " AND DATE_FORMAT(lastModified,'%Y-%m') = ?";

         return crmJdbcTemplate.queryForList(sql,new Object[]{staffId,date});

    }

    /**
     * 本月非正常结束的客户 GROUP BY areaId
     * @param date
     * @return
     */
    @Override
    public List<Map<String, Object>> findAreaCount(String date,int status) {
        String sql = "SELECT" +
                " cl.areaId as areaId,count( DISTINCT ( cl.id ) ) as count " +
                " FROM " +
                " clientele AS cl " +
                " INNER JOIN contract AS con ON cl.id = con.clienteleId " +
                " WHERE" +
                " DATE_FORMAT(con.lastModified,'%Y-%m') = ?" +
                " AND con.STATUS = ?" +
                " GROUP BY cl.areaId";

        return crmJdbcTemplate.queryForList(sql,new Object[]{date,status});
    }

    /**
     * 本月非正常结束的客户(教师) GROUP BY staffId
     * @param date
     * @param status
     * @return
     */
    @Override
    public List<Map<String, Object>> findStaffCount(String date,int status) {
        String sql = "SELECT" +
                " cl.staffId as staffId,count( DISTINCT ( cl.id ) ) as count " +
                " FROM " +
                " clientele AS cl " +
                " INNER JOIN contract AS con ON cl.id = con.clienteleId " +
                " WHERE" +
                " DATE_FORMAT(con.lastModified,'%Y-%m') = ?" +
                " AND con.STATUS = ?" +
                " GROUP BY cl.staffId";

        return crmJdbcTemplate.queryForList(sql,new Object[]{date,status});
    }

    @Override
    public Long findAreaClienteleCountByType(String areaId, int type) {
        String sql = "SELECT count(DISTINCT a.id) AS count " +
                " FROM clientele a " +
                " LEFT JOIN contract b ON a.id = b.clienteleId " +
                " WHERE b.type = ? " +
                " AND b.status = 1 " +
                " AND b.areaId = ?";
        return crmJdbcTemplate.queryForObject(sql,new Object[]{type,areaId},Long.class);
    }

    @Override
    public Long findStaffClienteleCountByType(String staffId, int type) {
        String sql = "SELECT count(DISTINCT a.id) AS count " +
                " FROM clientele a " +
                " LEFT JOIN contract b ON a.id = b.clienteleId " +
                " WHERE b.type = ? " +
                " AND b.status = 1 " +
                " AND b.staffId = ?";

        return crmJdbcTemplate.queryForObject(sql,new Object[]{type,staffId},Long.class);
    }

    /**
     * 查询园区下所有客户小孩打卡条数
     * @param areaId
     * @param classesCategory
     * @return
     */
    @Override
    public Long findAreaClienteleCheckInCountByType(String areaId, int classesCategory) {

        String sql = "SELECT count(id) AS count " +
                " FROM vega.check_record " +
                " WHERE " +
                " checkInTime is not null " +
                " AND childrenId in ( " +
                " SELECT id FROM vega.children " +
                " WHERE crmClienteleId in (" +
                " SELECT DISTINCT a.id  " +
                " FROM crm.clientele a " +
                " LEFT JOIN crm.contract b ON a.id = b.clienteleId " +
                " WHERE a.areaId = ?" +
                " AND b.status = 1 " +
                " AND b.type = ?)" +
                ")";

        return crmJdbcTemplate.queryForObject(sql,new Object[]{areaId,classesCategory},Long.class);
    }

    /**
     * 查询员工下所有客户小孩打卡条数
     * @param staffId
     * @param classesCategory
     * @return
     */
    @Override
    public Long findStaffClienteleCheckInCountByType(String staffId, int classesCategory) {

        String sql = "SELECT count(id) AS count " +
                " FROM vega.check_record " +
                " WHERE " +
                " checkInTime is not null " +
                " AND childrenId in ( " +
                " SELECT id FROM vega.children " +
                " WHERE crmClienteleId in (" +
                " SELECT DISTINCT a.id  " +
                " FROM crm.clientele a " +
                " LEFT JOIN crm.contract b ON a.id = b.clienteleId " +
                " WHERE a.staffId = ?" +
                " AND b.status = 1 " +
                " AND b.type = ?)" +
                ")";

        return crmJdbcTemplate.queryForObject(sql,new Object[]{staffId,classesCategory},Long.class);
    }

}
